package reports;

import java.io.Writer;
import java.sql.*;
import java.util.*; 

public class Report {

	public Report() throws ClassNotFoundException, SQLException {
		// Step 1: Load the JDBC driver. 
		Class.forName ("oracle.jdbc.driver.OracleDriver"); 
		// Step 2: Establish the connection to the database. 
		conn = DriverManager.getConnection(URL,"sgf2110","db123"); 
	}
	
	public TreeSet<String> getYearsToSearch() throws SQLException {
		
		TreeSet<String> dates = new TreeSet<String>(); 
		String query = new String();
	    Statement s = conn.createStatement();
	    
		query = "select distinct c.orderdate from customerorder c order by c.orderdate";  
		ResultSet r = null; 
        
        try {
        	r = s.executeQuery(query);
        	while (r.isAfterLast() == false) {
        		r.next();
        		dates.add(r.getString(1).substring(0, 4)); 
 
        	} 
        }
        catch (SQLException e) {
        	r.close(); 
        	s.close(); 
        	closeConnection(); 
        	return dates; 
        }

    	r.close(); 
    	s.close(); 
    	closeConnection();
        return dates; 
	}

	public void closeConnection() {
		try {
			conn.close();
		}
		catch (SQLException e) {}
	}

	public HashMap<String, ArrayList<Double>> getData(String startDate, String startMonth, String startYear, String endDate, String endMonth, String endYear, String orderType) throws SQLException {
	//public String getData(String startDate, String startMonth, String startYear, String endDate, String endMonth, String endYear, String orderType) throws SQLException {
				
		HashMap<String, ArrayList<Double>> data = new HashMap<String, ArrayList<Double>>(); 
		String query = new String();
		query = "select c.pid, c.quantity from customerorder c where c.orderType = \'" + orderType + "\' and (c.orderdate >=to_date(\'" + startDate + "-" + startMonth + "-" + startYear +"\') AND c.orderdate <= to_date(\'" + endDate + "-" + endMonth + "-" + endYear + "\')) order by c.pid";  
		
		ResultSet r = null; 
	    Statement s = conn.createStatement(); 
		
	    //pid and quantity: 
	    try {
	    	r = s.executeQuery(query); 
	    	while (r.isAfterLast() == false) {
	    		r.next(); 
	    		String pid = r.getString(1); 
	    		double quantity = r.getDouble(2); 
	    		
	    		if (data.containsKey(pid)) {
		    		ArrayList<Double> qData = data.get(pid);
		    		qData.set(0, quantity + qData.get(0));
		    		data.put(pid, qData); 
	    		}
	    		else {
		    		ArrayList<Double> qData = new ArrayList<Double>(); 
		    		qData.add(quantity); 
		    		data.put(pid, qData); 
	    		}
	    	}
	    }
	    catch (SQLException e) {

	    }
	   
	    //price
	    Set<String> keys = data.keySet(); 
	    Iterator itr = keys.iterator(); 
	    while (itr.hasNext()) {
	    	ResultSet r2 = null; 
	    	Statement s2 = conn.createStatement(); 
	    	String currentPid = (String) itr.next(); 
	    	query = "select p.price from product p where p.pid =\'" + currentPid + "\'"; 
	    	try {
	    		r2 = s.executeQuery(query); 
	    		r2.next(); 
	    		ArrayList<Double> prodData = data.get(currentPid); 
	    		double price = r2.getDouble(1); 
	    		prodData.add(price); 
	    		data.put(currentPid, prodData); 
	    	}
	    	catch (SQLException e) {
	    		//conn.close(); 
	    		//return e.toString(); 
	    	}
	    }
	    
		return data; 
	}
 
	
	public HashMap<String, String> getProductNames() throws SQLException {
		
		HashMap<String, String> prodNames = new HashMap<String, String>(); 
		String query = new String();
		query = "select p.pid, p.prodDesc from product p"; 
				
		ResultSet r = null; 
	    Statement s = conn.createStatement(); 
		
	    //pid and quantity: 
	    try {
	    	r = s.executeQuery(query); 
	    	while (r.isAfterLast() == false) {
	    		r.next(); 
	    		String pid = r.getString(1); 
	    		String prodDesc = r.getString(2); 
	    		prodNames.put(pid, prodDesc);
	    	}
	    }
	    catch (SQLException e) {
	    	//do nothing
	    }
	    return prodNames; 
	}
	
	public HashMap<String, Integer> getInventory() throws SQLException {
	//public String getInventory() throws SQLException {
	
		HashMap<String, Integer> inventory = new HashMap<String, Integer>(); 
		
		String query = new String();
		query = "select s.pid, s.quantity from supplierorder s"; 
				
		ResultSet r = null; 
	    Statement s = conn.createStatement(); 

	    //get supply 
	    try {
	    	r = s.executeQuery(query); 
	    	while (r.isAfterLast() == false) {
	    		r.next(); 
	    		String key = r.getString("PID"); 
	    		int quantity = r.getInt("QUANTITY"); 
	    		
	    		if (inventory.containsKey(key)) {
	    			inventory.put(key, inventory.get(key) + quantity); 
	    		}
	    		else {
	    			inventory.put(key, quantity); 
	    		}	
	    	}
	    }
	    catch (SQLException e) {
	    	//return "1* " + e.toString() + inventory.toString(); 
	    }
	    
	    //deduct purchases
	    r = null; 
		query = "select c.pid, c.quantity from customerorder c where c.ordertype =\'Purchase\'"; 
		try {
		    	r = s.executeQuery(query); 
		    	while (r.isAfterLast() == false) {
		    		r.next(); 
		    		String key = r.getString("PID"); 
		    		int quantity = r.getInt("QUANTITY"); 
		    		
		    		if (inventory.containsKey(key)) {
		    			inventory.put(key, inventory.get(key) - quantity); 
		    		}
		    	}
		    }
	    catch (SQLException e) {
	    	//return "2* " + e.toString() + inventory.toString(); 
	    }
	    
		//add back returns
		r = null; 
		query = "select c.pid, c.quantity from customerorder c where c.ordertype =\'Return\'"; 
		try {
		    	r = s.executeQuery(query); 
		    	while (r.isAfterLast() == false) {
		    		r.next(); 
		    		String key = r.getString("PID"); 
		    		int quantity = r.getInt("QUANTITY"); 
		    		
		    		if (inventory.containsKey(key)) {
		    			inventory.put(key, inventory.get(key) + quantity); 
		    		}
		    	}
		    }
	    catch (SQLException e) {
	    	//return "3* " + e.toString() + inventory.toString();
	    }
		
	    
		return inventory; 
	}
	
	public HashMap<String, Integer> getInventoryByDate(String startDate, String startMonth, String startYear) throws SQLException {
		//public String getInventoryByDate(String startDate, String startMonth, String startYear) throws SQLException {
		
			HashMap<String, Integer> inventory = new HashMap<String, Integer>(); 
			
			String query = new String();
			query = "select s.pid, s.quantity from supplierorder s where s.orderdate <=to_date(\'" + startDate + "-" + startMonth + "-" + startYear +"\')"; 
					
			ResultSet r = null; 
		    Statement s = conn.createStatement(); 

		    //get supply 
		    try {
		    	r = s.executeQuery(query); 
		    	while (r.isAfterLast() == false) {
		    		r.next(); 
		    		String key = r.getString("PID"); 
		    		int quantity = r.getInt("QUANTITY"); 
		    		
		    		if (inventory.containsKey(key)) {
		    			inventory.put(key, inventory.get(key) + quantity); 
		    		}
		    		else {
		    			inventory.put(key, quantity); 
		    		}	
		    	}
		    }
		    catch (SQLException e) {
		    	//return "1* " + e.toString() + inventory.toString(); 
		    }
		    
		    //deduct purchases
		    r = null; 
			query = "select c.pid, c.quantity from customerorder c where c.ordertype =\'Purchase\' and (c.orderdate <=to_date(\'" + startDate + "-" + startMonth + "-" + startYear +"\'))";  
			try {
			    	r = s.executeQuery(query); 
			    	while (r.isAfterLast() == false) {
			    		r.next(); 
			    		String key = r.getString("PID"); 
			    		int quantity = r.getInt("QUANTITY"); 
			    		
			    		if (inventory.containsKey(key)) {
			    			inventory.put(key, inventory.get(key) - quantity); 
			    		}
			    	}
			    }
		    catch (SQLException e) {
		    	//return "2* " + e.toString() + inventory.toString(); 
		    }
		    
			//add back returns
			r = null; 
			query = "select c.pid, c.quantity from customerorder c where c.ordertype =\'Return\' and (c.orderdate <=to_date(\'" + startDate + "-" + startMonth + "-" + startYear +"\'))"; 
			try {
			    	r = s.executeQuery(query); 
			    	while (r.isAfterLast() == false) {
			    		r.next(); 
			    		String key = r.getString("PID"); 
			    		int quantity = r.getInt("QUANTITY"); 
			    		
			    		if (inventory.containsKey(key)) {
			    			inventory.put(key, inventory.get(key) + quantity); 
			    		}
			    	}
			    }
		    catch (SQLException e) {
		    	//return "3* " + e.toString() + inventory.toString();
		    }
			
		    //return "PASSED " + inventory.toString(); 
			return inventory; 
		}
	
	protected Connection conn;
	private static final String URL = "jdbc:oracle:thin:@//w4111c.cs.columbia.edu:1521/ADB"; 
	private static final String URL2 = "jdbc:oracle:thin:@//w4111b.cs.columbia.edu:1521/ADB";
}
